package sf.database.mapper.handle;

import org.jooq.Condition;
import org.jooq.Delete;
import org.jooq.OrderField;
import org.jooq.Select;
import org.jooq.SelectConditionStep;
import org.jooq.Update;
import org.jooq.impl.DSL;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.mapping.PropertyPath;
import org.springframework.data.repository.query.DefaultParameters;
import org.springframework.data.repository.query.Parameters;
import org.springframework.data.repository.query.ParametersParameterAccessor;
import org.springframework.data.repository.query.ParametersSource;
import org.springframework.data.repository.query.parser.Part;
import org.springframework.data.repository.query.parser.PartTree;
import sf.common.wrapper.Page;
import sf.core.DBField;
import sf.database.dao.DBClient;
import sf.database.dialect.DBDialect;
import sf.database.meta.ColumnMapping;
import sf.database.meta.MetaHolder;
import sf.database.meta.TableMapping;
import sf.jooq.JooqTables;
import sf.jooq.tables.JooqTable;
import sf.tools.ArrayUtils;

import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.lang.reflect.TypeVariable;
import java.lang.reflect.WildcardType;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

import static org.springframework.data.repository.query.parser.Part.Type.TRUE;

/**
 * 使用spring-data-commons实现,使用方法和spring-data-jpa一致
 * 自动生成sql
 */
public class MethodHandleJooqAutoSQL {
    protected static MethodHandleJooqAutoSQL ms = new MethodHandleJooqAutoSQL();

    public static MethodHandleJooqAutoSQL getInstance() {
        return ms;
    }

    private MethodHandleJooqAutoSQL() {

    }

    public Object call(DBClient dbClient, Class entityClass, Method m, Object[] params) {
        DBDialect dialect = dbClient.getDialect(false);
        Object ret = null;
        PartTree tree = new PartTree(m.getName(), entityClass);
        if (tree.isDelete()) {
            Delete delete = createDelete(dialect, tree, entityClass, m, params);
            ret = parseUpdate(null, delete, m, dbClient);
        } else {
            List<Select> selects = createQuery(dialect, tree, entityClass, m, params, true);
            ret = parseSelectList(m, dbClient, selects);
        }
        return ret;
    }


    private List<Select> createQuery(DBDialect dialect, PartTree tree, Class entityClass, Method m, Object[] params, boolean withPageSort) {
        JooqTable table = JooqTables.getTable(null,entityClass, dialect);
        TableMapping metadata = MetaHolder.getMeta(entityClass);
        Parameters parameters = new DefaultParameters(ParametersSource.of(m));
        ParametersParameterAccessor accessor = new ParametersParameterAccessor(
                parameters, params);
        List<Condition> or = getConditions(tree, table, metadata, parameters, accessor);
        List<Select> selects = new ArrayList<>();
        Select select = null;
        Select countSelect = null;
        boolean isPage = false;
        Pageable page = accessor.getPageable();
        if (tree.isCountProjection()) {
            select = DSL.selectCount().from(table).where(DSL.or(or));
        } else if (tree.isDistinct()) {
            select = DSL.selectDistinct(table.fields()).from(table).where(DSL.or(or));
        } else if (tree.isExistsProjection()) {
            select = DSL.selectCount().from(table).where(DSL.or(or)).limit(1);
        } else {
            select = DSL.select(table.fields()).from(table).where(DSL.or(or));
            if (page != Pageable.unpaged() && !tree.isCountProjection() && !tree.isExistsProjection()) {
                isPage = true;
                countSelect = DSL.selectCount().from(table).where(DSL.or(or));
            }
        }

        List<OrderField> list = new ArrayList<>();
        if (withPageSort) {
            Sort sort = tree.getSort();
            if (accessor.getSort() != null) {
                sort = accessor.getSort();
            }
            if (page != Pageable.unpaged() && page.getSort() != null) {
                sort = page.getSort();
            }
            if (sort != null)
                list = setSortToSpec(table, sort, metadata);
        }
        if (!list.isEmpty()) {
            ((SelectConditionStep) select).orderBy(list);
        }
        selects.add(select);
        if (isPage) {
            ((SelectConditionStep) select).offset((int) page.getOffset()).limit(page.getPageSize());
            selects.add(countSelect);
        }

        return selects;
    }

    private List<OrderField> setSortToSpec(JooqTable table, Sort sort, TableMapping meta) {
        List<OrderField> list = new ArrayList<>();
        for (Sort.Order order : sort) {
            ColumnMapping column = meta.findField(order.getProperty());
            if (column != null) {
                DBField field = column.getField();
                if (order.isAscending()) {
                    list.add(table.c(field).asc());
                } else {
                    list.add(table.c(field).desc());
                }
            }
        }
        return list;
    }

    private int getBindParamIndex(int index, String fieldName, Parameters parameters) {
        for (int i = 0; i < parameters.getNumberOfParameters(); i++) {
            org.springframework.data.repository.query.Parameter param = parameters.getParameter(i);
            if (param.getName().isPresent()) {
                if (fieldName.equals(param.getName().get())) {
                    return i;
                }
            } else {
                if (index == param.getIndex()) {
                    return i;
                }
            }
        }
        throw new NoSuchElementException("Can not found bind parameter '"
                + fieldName + "' in method ");
    }

    /**
     * Builds a JPA {@link Predicate} from the underlying {@link Part}.
     * @param part
     * @param p
     * @param field
     * @param value
     * @return
     */
    public Condition build(Part part, JooqTable p, DBField field, Object value) {

        PropertyPath property = part.getProperty();
        Part.Type type = part.getType();

        switch (type) {
            case BETWEEN:
                Object[] objs = null;
                if (value.getClass().isArray()) {
                    objs = ArrayUtils.objectToArray(value);
                }
                return p.column(field).between(objs[0], objs[1]);
            case AFTER:
            case GREATER_THAN:
                return p.c(field).gt(value);
            case GREATER_THAN_EQUAL:
                return p.c(field).ge(value);
            case BEFORE:
            case LESS_THAN:
                return p.c(field).lt(value);
            case LESS_THAN_EQUAL:
                return p.c(field).le(value);
            case IS_NULL:
                return p.c(field).isNull();
            case IS_NOT_NULL:
                return p.c(field).isNotNull();
            case NOT_IN:
                return p.c(field).notIn((Collection<?>) value);
            case IN:
                return p.c(field).in((Collection<?>) value);
            case STARTING_WITH:
                return p.c(field).like("%" + value, '\\');
            case ENDING_WITH:
                return p.c(field).like(value + "%", '\\');
            case CONTAINING:
                return p.c(field).like("%" + value + "%", '\\');
            case NOT_CONTAINING:
                return p.c(field).notLike("%" + value + "%", '\\');
            case LIKE:
                return p.c(field).like("%" + value + "%", '\\');
            case NOT_LIKE:
                return p.c(field).notLike("%" + value + "%", '\\');
            case TRUE:
            case FALSE:
                return type.equals(TRUE) ? p.c(field).isTrue() : p.c(field).isFalse();
            case SIMPLE_PROPERTY:
                return p.c(field).eq(value);
            case NEGATING_SIMPLE_PROPERTY:
                return p.c(field).ne(value);
            case IS_EMPTY:
            case IS_NOT_EMPTY:
                if (!property.getLeafProperty().isCollection()) {
                    throw new IllegalArgumentException("IsEmpty / IsNotEmpty can only be used on collection properties!");
                }
//                return type.equals(IS_NOT_EMPTY) ? null : null;

            default:
                throw new IllegalArgumentException("Unsupported keyword " + type);
        }
    }

    private Delete createDelete(DBDialect dialect, PartTree tree, Class entityClass, Method m, Object[] params) {
        JooqTable table = JooqTables.getTable(null,entityClass, dialect);
        TableMapping metadata = MetaHolder.getMeta(entityClass);
        Parameters parameters = new DefaultParameters(ParametersSource.of(m));
        ParametersParameterAccessor accessor = new ParametersParameterAccessor(
                parameters, params);
        List<Condition> or = getConditions(tree, table, metadata, parameters, accessor);
        Delete delete = null;
        if (tree.isDelete()) {
            delete = DSL.deleteFrom(table).where(DSL.or(or));
        }
        return delete;
    }

    private List<Condition> getConditions(PartTree tree, JooqTable table, TableMapping metadata, Parameters parameters, ParametersParameterAccessor accessor) {
        int index = 0;
        List<Condition> or = new ArrayList<>();
        for (PartTree.OrPart node : tree) {
            List<Condition> and = new ArrayList<>();
            for (Part part : node) {
                PropertyPath path = part.getProperty();
                if (path.getOwningType().getType() != metadata.getThisType()) {
                    throw new IllegalArgumentException("PathType:"
                            + path.getOwningType().getType() + "  metadata:"
                            + metadata.getThisType());
                }
                String fieldName = path.getSegment();
                ColumnMapping field = metadata.findField(fieldName);
                int paramInfo = getBindParamIndex(index++, fieldName, parameters);
                Object obj = accessor.getBindableValue(paramInfo);
                if (field != null) {
                    and.add(build(part, table, field.getField(), obj));
                }
            }
            or.add(DSL.and(and));
        }
        return or;
    }

    /**
     * 根据返回参数int 或者int[] 判断是否是批处理。如果都没有，根据第一参数判断
     * @param update
     * @param delete
     * @param method
     * @param dbClient
     * @return
     */
    protected Object parseUpdate(Update update, Delete delete, Method method, DBClient dbClient) {
        if (update != null) {
            return dbClient.getJooq().jooqUpdate(update);
        } else if (delete != null) {
            return dbClient.getJooq().jooqDelect(delete);
        }
        return null;
    }

    protected Object parseSelectList(Method method, DBClient dbClient, List<Select> selects) {
        Select select = null;
        Select countSelect = null;
        if (!selects.isEmpty()) {
            select = selects.get(0);
            if (selects.size() > 1) {
                countSelect = selects.get(1);
            }
        }
        Class<?> returnType = method.getReturnType();
        Type retType = method.getGenericReturnType();
        Class[] paraClass = method.getParameterTypes();
        if (returnType == Page.class) {
            Class resultType = getPageType(retType, Map.class);
            return dbClient.getJooq().jooqSelectPage(countSelect, select, resultType);
            //else否则就默认为mapper类型
        }
        if (Map.class.isAssignableFrom(returnType)) {
            //如果定义返回结果为Map，无论是否泛型，都认为返回一个Map
            return dbClient.getJooq().jooqSelectOne(select, Map.class);
        } else if (List.class.isAssignableFrom(returnType)) {
            Class type = getType(retType);
            if (type == null) {
                type = Map.class;
            }
            return dbClient.getJooq().jooqSelectList(select, returnType);
        }

        //更改类型为Single
        return dbClient.getJooq().jooqSelectOne(select, returnType);

    }

    protected Class getPageType(Type type, Class defaultClass) {
        if (type instanceof Class) {
            return defaultClass;
        } else {
            Type t = ((ParameterizedType) type)
                    .getActualTypeArguments()[0];
            if (t instanceof ParameterizedType) {
                return getParamterTypeClass(t);
            } else if (t instanceof WildcardType || t instanceof TypeVariable) {
                //丢失类型，只能用返回类型来判断
                return defaultClass;
            } else {
                return (Class) t;
            }
        }
    }

    protected Class getType(Type type) {
        if (type instanceof Class) {
            return (Class) type;
        } else if (type instanceof ParameterizedType) {
            Type t = ((ParameterizedType) type)
                    .getActualTypeArguments()[0];
            if (t instanceof ParameterizedType) {
                return getParamterTypeClass(t);
            } else {
                return (Class) t;
            }
        } else {

            return null;
        }
    }

    protected Class getParamterTypeClass(Type t) {
        if (t instanceof Class) {
            return (Class) t;
        } else {
            return (Class) ((ParameterizedType) t).getRawType();
        }

    }

    private static String getFirstToken(String sql) {
        boolean start = false;
        int startIndex = 0;
        for (int i = 0; i < sql.length(); i++) {
            char c = sql.charAt(i);
            if (!start) {
                if (!isSpecialChar(c)) {
                    start = true;
                    startIndex = i;
                }
                continue;
            }

            if (isSpecialChar(c)) {
                return sql.substring(startIndex, i).toLowerCase();
            }

        }
        return "";
    }

    private static boolean isSpecialChar(char c) {
        return c == ' ' || c == '\t' || c == '\r' || c == '\n';
    }

}
